﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Security;

namespace GrayParrot.Web.Security
{
    [Serializable]
    public static class Pages
    {
        public static string ConnectionString { get; set; }
        public static string ApplicationName { get; set; }
        public static Exception LastException { get; set; }

        /// <summary>
        /// Creates the page.
        /// </summary>
        /// <param name="pageName">Name of the page.</param>
        /// <remarks>
        /// Pages are not system page by default....
        /// </remarks>
        public static void CreatePage(string pageName, bool systemPage = false)
        {
            try
            {
                SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                p1.Value = ApplicationName;
                SqlParameter p2 = new SqlParameter("@PageName ", SqlDbType.NVarChar, 256);
                p2.Value = pageName;
                SqlParameter p3 = new SqlParameter("@SystemPage ", SqlDbType.Bit);
                p3.Value = systemPage;
                SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                pRT.Direction = ParameterDirection.ReturnValue;

                SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Pages_CreatePage]", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 600; // seconds
                        cmd.Parameters.AddRange(m_params);

                        conn.Open();

                        cmd.ExecuteNonQuery();

                        int returnValue = GetReturnValue(cmd);

                        switch (returnValue)
                        {
                            case 0:
                                return;
                            case 1:
                                throw new Exception(string.Format("Page already exists: {0}", pageName));
                            default:
                                throw new Exception("Unknown failure");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LastException = ex;
                throw;
            }
        }

        /// <summary>
        /// Deletes the page.
        /// </summary>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        public static bool DeletePage(string pageName)
        {
            bool retValue = false;

            try
            {
                SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                p1.Value = ApplicationName;
                SqlParameter p2 = new SqlParameter("@PageName ", SqlDbType.NVarChar, 256);
                p2.Value = pageName;
                SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                pRT.Direction = ParameterDirection.ReturnValue;

                SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Pages_DeletePage]", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 600; // seconds
                        cmd.Parameters.AddRange(m_params);

                        conn.Open();

                        cmd.ExecuteNonQuery();

                        int returnValue = GetReturnValue(cmd);

                        if (returnValue == 2)
                        {
                            throw new Exception("Page is not empty");
                        }

                        retValue = (returnValue == 0);
                    }
                }
            }
            catch (Exception ex)
            {
                LastException = ex;
                throw;
            }

            return retValue;
        }

        /// <summary>
        /// Determines whether [is page in role] [the specified pagename].
        /// </summary>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleName">Name of the role.</param>
        /// <returns>
        ///   <c>true</c> if [is page in role] [the specified pagename]; otherwise, <c>false</c>.
        /// </returns>
        /// <exception cref="System.Exception">Unknown failure</exception>
        public static bool IsPageInRole(string pagename, string roleName)
        {
            bool retValue = false;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pagename;
            SqlParameter p3 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p3.Value = roleName;

            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_IsPageInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    int iStatus = GetReturnValue(cmd);

                    switch (iStatus)
                    {
                        case 0:
                            retValue = false;
                            break;
                        case 1:
                            retValue = true;
                            break;
                        case 2:
                            retValue = false;
                            break;
                        // throw new ProviderException(SR.GetString(SR.Provider_user_not_found));
                        case 3:
                            retValue = false; // throw new ProviderException(SR.GetString(SR.Provider_role_not_found, roleName));
                            break;
                        default:
                            throw new Exception("Unknown failure");
                    }
                    
                }
            }

            return retValue;
        }

        /// <summary>
        /// Adds the pages to role.
        /// </summary>
        /// <param name="pagenames">The pagenames.</param>
        /// <param name="roleName">Name of the role.</param>
        public static void AddPagesToRole(string[] pagenames, string roleName)
        {
            AddPagesToRoles(pagenames, new string[] { roleName });
        }

        /// <summary>
        /// Adds the pages to roles.
        /// </summary>
        /// <param name="pagenames">The pagenames.</param>
        /// <param name="roleNames">The role names.</param>
        /// <exception cref="System.Exception">
        /// Role name not found.
        /// or
        /// Page is already in role.
        /// </exception>
        /// <exception cref="System.ArgumentException">Page names cannot contain commas.</exception>
        public static void AddPagesToRoles(string[] pagenames, string[] roleNames)
        {
            string s1 = String.Empty, s2 = String.Empty;

            foreach (string rolename in roleNames)
            {
                if (!Roles.RoleExists(rolename))
                {
                    throw new Exception("Role name not found.");
                }
            }

            foreach (string pagename in pagenames)
            {
                if (pagename.Contains(","))
                {
                    throw new ArgumentException("Page names cannot contain commas.");
                }

                foreach (string rolename in roleNames)
                {
                    if (IsPageInRole(pagename, rolename))
                    {
                        throw new Exception("Page is already in role.");
                    }
                }
            }

            int numPagesRemaing = pagenames.Length;
            while (numPagesRemaing > 0)
            {
                int iter;
                string allPages = pagenames[pagenames.Length - numPagesRemaing];
                numPagesRemaing--;
                for (iter = pagenames.Length - numPagesRemaing; iter < pagenames.Length; iter++)
                {
                    if (allPages.Length + pagenames[iter].Length + 1 >= 4000)
                        break;
                    allPages += "," + pagenames[iter];
                    numPagesRemaing--;
                }

                int numRolesRemaining = roleNames.Length;
                while (numRolesRemaining > 0)
                {
                    string allRoles = roleNames[roleNames.Length - numRolesRemaining];
                    numRolesRemaining--;
                    for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++)
                    {
                        if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                            break;
                        allRoles += "," + roleNames[iter];
                        numRolesRemaining--;
                    }

                    SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                    p1.Value = ApplicationName;
                    SqlParameter p2 = new SqlParameter("@PageNames", SqlDbType.NVarChar, 4000);
                    p2.Value = allPages;
                    SqlParameter p3 = new SqlParameter("@RoleNames", SqlDbType.NVarChar, 4000);
                    p3.Value = allRoles;
                    SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    pRT.Direction = ParameterDirection.ReturnValue;

                    SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_AddPagesToRoles]", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 600; // seconds
                            cmd.Parameters.AddRange(m_params);

                            conn.Open();

                            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                            if (reader.Read())
                            {
                                if (reader.FieldCount > 0)
                                    s1 = reader.GetString(0);
                                if (reader.FieldCount > 1)
                                    s2 = reader.GetString(1);
                            }

                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    return;
                                case 1:
                                    throw new Exception(string.Format("This page not found: {0} ", s1));
                                case 2:
                                    throw new Exception(string.Format("Page not found: {0}", s1));
                                case 3:
                                    throw new Exception(string.Format("This page already in role: {0} {1}", s1, s2));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Adds the page to role.
        /// </summary>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleName">Name of the role.</param>
        public static void AddPageToRole(string pagename, string roleName)
        {
            AddPagesToRoles(new string[] { pagename }, new string[] { roleName });
        }

        /// <summary>
        /// Adds the page to roles.
        /// </summary>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleNames">The role names.</param>
        public static void AddPageToRoles(string pagename, string[] roleNames)
        {
            AddPagesToRoles(new string[] { pagename }, roleNames);
        }

        public static void RemovePageFromRole(string pagename, string roleName)
        {
            RemovePagesFromRoles(new string[] { pagename }, new string[] {roleName} );
        }

        public static void RemovePageFromRoles(string pagename, string[] roleNames)
        {
            RemovePagesFromRoles(new string[] { pagename }, roleNames);
        }

        public static void RemovePagesFromRole(string[] pagenames, string roleName)
        {
            RemovePagesFromRoles(pagenames, new string[] { roleName });
        }

        public static void RemovePagesFromRoles(string[] pagenames, string[] roleNames)
        {
            string s1 = String.Empty, s2 = String.Empty;

            int numPagesRemaing = pagenames.Length;
            while (numPagesRemaing > 0)
            {
                int iter;
                string allPages = pagenames[pagenames.Length - numPagesRemaing];
                numPagesRemaing--;
                for (iter = pagenames.Length - numPagesRemaing; iter < pagenames.Length; iter++)
                {
                    if (allPages.Length + pagenames[iter].Length + 1 >= 4000)
                        break;
                    allPages += "," + pagenames[iter];
                    numPagesRemaing--;
                }

                int numRolesRemaining = roleNames.Length;
                while (numRolesRemaining > 0)
                {
                    string allRoles = roleNames[roleNames.Length - numRolesRemaining];
                    numRolesRemaining--;
                    for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++)
                    {
                        if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                            break;
                        allRoles += "," + roleNames[iter];
                        numRolesRemaining--;
                    }

                    /*  ccccc */
                    SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                    p1.Value = ApplicationName;
                    SqlParameter p2 = new SqlParameter("@PageNames", SqlDbType.NVarChar, 4000);
                    p2.Value = allPages;
                    SqlParameter p3 = new SqlParameter("@RoleNames", SqlDbType.NVarChar, 4000);
                    p3.Value = allRoles;
                    SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    pRT.Direction = ParameterDirection.ReturnValue;

                    SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_RemovePagesFromRoles]", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 600; // seconds
                            cmd.Parameters.AddRange(m_params);

                            conn.Open();

                            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                            if (reader.Read())
                            {
                                if (reader.FieldCount > 0)
                                    s1 = reader.GetString(0);
                                if (reader.FieldCount > 1)
                                    s2 = reader.GetString(1);
                            }

                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    return;
                                case 1:
                                    throw new Exception(string.Format("This page not found: {0}", s1));
                                case 2:
                                    throw new Exception(string.Format("Role not found: {0}", s2));
                                case 3:
                                    throw new Exception(string.Format("This page already not in role: {0} {1}", s1, s2));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }
        }

        public static string[] GetAllPages()
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;
            
            SqlParameter[] m_params = new SqlParameter[2] { p1, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Pages_GetAllPages]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add( reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);
                    }
                }
            }

            return retValue;
        }

        public static string[] GetRolesForPage(string pageName)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;

            SqlParameter[] m_params = new SqlParameter[2] { p1, p2 };
           
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_GetRolesForPage]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);

                        if (sc.Count < 1)
                        {
                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    retValue = null;
                                    break;
                                case 1:
                                    retValue = null;
                                    break;
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }

            return retValue;
        }

        public static string[] GetPagesInRole(string roleName)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p2.Value = roleName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_GetPagesInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);

                        if (sc.Count < 1)
                        {
                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    retValue = null;
                                    break;
                                case 1:
                                    throw new Exception(string.Format("Role not found: {0}", roleName));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }

            return retValue;
        }

        public static bool PageExists(string pageName)
        {
            bool retValue = false;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Pages_PageExists]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    int returnValue = GetReturnValue(cmd);

                    switch (returnValue)
                    {
                        case 0:
                            retValue = false;
                            break;
                        case 1:
                            retValue = true;
                            break;
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }

        public static string[] FindPagesInRole(string roleName, string pagenameToMatch)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p2.Value = roleName;
            SqlParameter p3 = new SqlParameter("@PageNameToMatch", SqlDbType.NVarChar, 256);
            p3.Value = pagenameToMatch;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_GetPagesInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    StringCollection sc = new StringCollection();
                    SqlDataReader reader = null;

                    try
                    {
                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                sc.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }
                    
                    retValue = new string[sc.Count];
                    sc.CopyTo(retValue, 0);

                    if (sc.Count < 1)
                    {
                        switch (GetReturnValue(cmd))
                        {
                            case 0:
                                retValue = null;
                                break;
                            case 1:
                                throw new Exception(string.Format("Role not found: {0}", roleName));
                            default:
                                throw new Exception("Unknown failure");
                        }
                    }

                }
            }

            return retValue;
        }

        private static int GetReturnValue(SqlCommand cmd)
        {
            foreach (SqlParameter param in cmd.Parameters)
            {
                if (param.Direction == ParameterDirection.ReturnValue && param.Value != null && param.Value is int)
                    return (int)param.Value;
            }
            return -1;
        }

        public static DataTable GetPagesInRoles()
        {
            DataTable retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[2] { p1, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_PagesInRoles_GetPagesInRoles]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    retValue = new DataTable();
                    adp.Fill(retValue);

                    switch (GetReturnValue(cmd))
                    {
                        case 0:
                            break;
                        case 1:
                            throw new Exception(string.Format("ApplicationName not found: {0}", ApplicationName));
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }

        public static Guid GetPageId(string pageName)
        {
            Guid retValue = Guid.Empty;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@pageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Pages_GetPageId]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = null;
                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retValue = Guid.Parse(reader.GetString(0));
                        }
                    }

                    switch (GetReturnValue(cmd))
                    {
                        case 0:
                            break;
                        case 1:
                            throw new Exception(string.Format("ApplicationName not found: {0}", ApplicationName));
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }
    }
}
